﻿using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Reflection;
using Gammar.Digger.SystemFramework;
using Gammar.Digger.Entity;
using Gammar.Digger.IDAL;
using Gammar.Digger.Data;
namespace Gammar.Digger.DALSqlServer
{
    public class DbHelper : DbSqlServer, IDbHelper
    {
        public DbHelper(string conn):base(conn)
        {
        }
        
        #region IDbHelper Members

        public bool Truncate(string tableName)
        {
            try
            {
                string sql = string.Format("truncate table {0}", tableName);
                ExecuteNonQuery(sql);
                return true;

            }
            catch (Exception ex)
            {
                
                LogUtility.WriteErrLog(ex.Message);
                return false;
            }
        }

        public bool IsExistTable(string tableName)
        {
            string sql=string.Format(@"select count(*)
            from  sysobjects
           where  id = object_id('{0}')
            and   type = 'U'",tableName);
            object obj = ExecuteCell(sql);

            int result = (int)obj;
            return result == 1 ? true : false;
        }

        public bool CreateDataTable(string tableName)
        {
            string pk = "PK_" + tableName;
            string sql = string.Format(@"create table {0} (
   data_id              int                  identity(1, 1),
   title                varchar(5000)         collate Chinese_PRC_CI_AS null,
   detail               text                 collate Chinese_PRC_CI_AS null,
   original_price       varchar(1000)         null,
   sale_price           varchar(1000)         null,
   image                varchar(1000)         collate Chinese_PRC_CI_AS null,
   create_time          varchar(1000)         null,
   source_name          varchar(1000)         collate Chinese_PRC_CI_AS null,
   image_path           varchar(1000)         collate Chinese_PRC_CI_AS null,
   last_modify_time     bigint               null,
   brand                varchar(1000)         collate Chinese_PRC_CI_AS null,
   url_id               uniqueidentifier     null,
   constraint {1} primary key (data_id)
         on 'PRIMARY'
)
on 'PRIMARY'
textimage_on 'PRIMARY'", tableName, pk);
            try
            {
                ExecuteNonQuery(sql);
                return true;
            }
            catch (Exception ex)
            {
                LogUtility.WriteErrLog(ex.Message);
                return false;
            }
        }

        public bool CreateQueueTable(string tableName)
        {
            string pk = "PK_" + tableName;
            string sql = string.Format(@"create table {0} (
   queue_id             int                  not null,
   url                  varchar(500)         not null,
   url_status           tinyint              null,
   plugin_run_status_id int                  null,
   create_time          datetime             null,
   constraint {1} primary key (queue_id)
)
go", tableName, pk);
            //Added by Jimmy Yin 
            //Purpose:It's for test to check whether you can get it or not.
            return true;
        }

        public bool CreateUrlTable(string tableName)
        {
            string pk = "PK_" + tableName;
            //string sql = string.Format(@"create table {0} (url_id int identity(1, 1), name varchar(100) collate Chinese_PRC_CI_AS null,url varchar(100) collate Chinese_PRC_CI_AS null, is_valid bit null,constraint {1} primary key (url_id) on 'PRIMARY')on 'PRIMARY'", tableName, pk);
            string sql = string.Format(@"create table {0} (
   url_id               uniqueidentifier     not null,
   queue_id             int                  null,
   name                 varchar(1000)         collate Chinese_PRC_CI_AS null,
   url                  varchar(1000)         collate Chinese_PRC_CI_AS null,
   read_status          bit                  null,
   create_time          datetime             null,
   last_change_time     datetime             null,
   constraint {1} primary key (url_id)
         on 'PRIMARY'
)
on 'PRIMARY'", tableName, pk); 
            try
            {
                ExecuteNonQuery(sql);
                return true;
            }
            catch (Exception ex)
            {
                LogUtility.WriteErrLog(ex.Message);
                return false;
            }
        }

        public bool CreateCommentsTable(string tableName)
        {
            string pk = "PK_" + tableName;
            string sql = string.Format(@"create table {0} (
   comment_id           int                  identity(1, 1),
   point                int                  null,
   title                varchar(1000)         collate Chinese_PRC_CI_AS null,
   detail               text                 collate Chinese_PRC_CI_AS null,
   author               varchar(500)          collate Chinese_PRC_CI_AS null,
   create_time          bigint               null,
   url_id               uniqueidentifier     null,
   constraint {1} primary key (comment_id)
         on 'PRIMARY'
)
on 'PRIMARY'
textimage_on 'PRIMARY'", tableName, pk);
            try
            {
                ExecuteNonQuery(sql);
                return true;
            }
            catch (Exception ex)
            {
                LogUtility.WriteErrLog(ex.Message);
                return false;
            }
          
        }

        /// <summary>
        /// 获取重复数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataTable GetDuplicateData(string tableName)
        {
            string sql =string.Format( @"select url_id from {0} t where exists(select top 1 url_id from {0} where url=t.url and  url_id<t.url_id)", tableName);

            try
            {
                return  ExecuteAdapter(sql);
            }
            catch (Exception ex)
            {
                 LogUtility.WriteErrLog(ex.Message);
                 return null;
            }
        }

        public bool DeleteDuplicateData(string tableName)
        {
            string sql =string.Format(@"delete from {0} where url_id in
(
select url_id
  from   {0}   t  
  where   exists(select  top 1 url_id   from   {0}   where   url=t.url  and  url_id<t.url_id)
)", tableName);

            try
            {
                ExecuteNonQuery(sql);

                return true;
            }
            catch (Exception ex)
            {
                LogUtility.WriteErrLog(ex.Message);
                return false;
            }
        }

        #endregion
    }
}
